﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop;
using System.Data.OleDb;
using System.Diagnostics;

namespace HistoricalLoansImporter
{
    public partial class frmImport : Form
    {
        public frmImport()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (excelFileOpenDialog.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = excelFileOpenDialog.FileName;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(txtFileName.Text))
            {
                MessageBox.Show(this, "Invalid File Path", "History Importer", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (!File.Exists(txtFileName.Text))
            {
                MessageBox.Show(this, "File Not Exists", "History Importer", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (string.IsNullOrWhiteSpace(txtDatabaseName.Text) || string.IsNullOrWhiteSpace(txtDatabaseName.Text))
            {
                MessageBox.Show(this, "Invalid Server and Database name", "History Importer", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            try
            {
                string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", txtServerName.Text, txtDatabaseName.Text);
                DatabaseDataContext db = new DatabaseDataContext(connectionString);


                DataSet dsExcelData = ReadExcelSheet(txtFileName.Text, txtSheet.Text);
                pbProgress.Minimum = 0;
                pbProgress.Maximum = dsExcelData.Tables[0].Rows.Count;
                pbProgress.Value = 0;
                pbProgress.Visible = true;

                int sucess = 0,
                    fail = 0;

                foreach (DataRow drHistory in dsExcelData.Tables[0].Rows)
                {
                    try
                    {
                        CustomerLoanHistory loanHistory = new CustomerLoanHistory();

                        loanHistory.LoanHistoryID = Guid.NewGuid();

                        if (drHistory["Customer Name"] != DBNull.Value)
                            loanHistory.CustomerName = drHistory["Customer Name"].ToString();                    
                        if (drHistory["Discreet Delay Days"] != DBNull.Value)
                            loanHistory.LastDiscreteDelay = int.Parse(drHistory["Discreet Delay Days"].ToString());
                        if (drHistory["consecutive Delay Days"] != DBNull.Value)
                            loanHistory.LastConsecutiveDelay = int.Parse(drHistory["consecutive Delay Days"].ToString());
                        if (drHistory["Execution Date"] != DBNull.Value)
                            loanHistory.LoanExecutionDate = DateTime.Parse(drHistory["Execution Date"].ToString());
                        if (drHistory["Closer Type"] != DBNull.Value)
                            loanHistory.LoanHistoryCloseTypeID = GetCloserTypeID(drHistory["Closer Type"].ToString(), db);
                        if (drHistory["Stage"] != DBNull.Value)
                            loanHistory.LoanProjectStageID = GetProjectStageID(int.Parse(drHistory["Stage"].ToString()), db);
                        if (drHistory["Notes"] != DBNull.Value)
                            loanHistory.Notes = drHistory["Notes"].ToString();
                        if (drHistory["Old Code"] != DBNull.Value)
                            loanHistory.OldCustomerCode = drHistory["Old Code"].ToString();
                        if (drHistory["Closer Date"] != DBNull.Value)
                            loanHistory.LoanCloseDate = DateTime.Parse(drHistory["Closer Date"].ToString());

                   
                        db.CustomerLoanHistories.InsertOnSubmit(loanHistory);
                        db.SubmitChanges();
                        pbProgress.Value++;
                        pbProgress.Update();
                        sucess++;
                    }
                    catch
                    {
                        fail++;
                        continue;
                        
                    }
                }

                MessageBox.Show(this, sucess + " Historical Data Row(s) Imported Sucessfully" + ((fail > 0) ? " And " + fail.ToString() + " Failed.": "."), "History Importer", MessageBoxButtons.OK, MessageBoxIcon.Information);
                pbProgress.Visible = false;
                
            }
            catch (Exception exp)
            {
                string s = exp.Message;
                MessageBox.Show(s);
            }



        }

        private Guid? GetProjectStageID(int stage, DatabaseDataContext db)
        {
            var foundStage = (from projectStage in db.ProjectStages
                                   where projectStage.Stage == stage
                                  select projectStage).First();

            if (foundStage != null)
                return foundStage.ProjectStageID;

            return null;
        }

        private Guid? GetCloserTypeID(string closerType, DatabaseDataContext db)
        {
            var foundType = (from closerTypes in db.LoanHistoryCloseTypes
                              where closerTypes.LoanHistoryCloseTypeName.Trim() == closerType.Trim()
                              select closerTypes).First();

            if (foundType != null)
                return foundType.LoanHistoryCloseTypeID;

            return null;
        }

        private DataSet ReadExcelSheet(string path, string sheetName)
        {
            if (path.Trim() == string.Empty || !System.IO.File.Exists(path))
                return null;

            string ConnectString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
            + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";

            OleDbConnection Connection = new OleDbConnection();
            OleDbDataAdapter Command;
            DataSet dsExcelData;
            Connection.ConnectionString = ConnectString;
            try
            {
                Connection.Open();
                Command = new OleDbDataAdapter("SELECT * FROM[" + sheetName + "$]", Connection);
                dsExcelData = new DataSet();
                Command.Fill(dsExcelData);
                dsExcelData.Tables[0].TableName = sheetName;
                ClearEmptyRows(ref dsExcelData);
            }
            catch (OleDbException ex)
            {
                Debug.Assert(false, String.Format("Error {0},StackTrack {1}",
                    ex.Message, ex.StackTrace.ToString()));
                return null;
            }

            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }

            return dsExcelData;
        }

        private void ClearEmptyRows(ref DataSet dsExcelData)
        {
            for (int iRowIdx = dsExcelData.Tables[0].Rows.Count - 1; iRowIdx >= 0; iRowIdx--)
            {
                bool isEmpty = true;
                foreach (DataColumn dColumn in dsExcelData.Tables[0].Columns)
                {
                    object value = dsExcelData.Tables[0].Rows[iRowIdx][dColumn.ColumnName];
                    if (value.ToString().Trim() != string.Empty)
                        isEmpty = false;
                }
                if (isEmpty)
                    dsExcelData.Tables[0].Rows.RemoveAt(iRowIdx);
            }
        }
    }
}
